package com.teamsun.service;

import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.TypeReference;
import com.teamsun.datasource.DatasourceHolder;
import com.teamsun.entity.DBCfg;
import com.teamsun.entity.OffLineDownLoad;
import com.teamsun.entity.RptResult;
import com.teamsun.entity.TableHeader;
import com.teamsun.exception.ServiceException;
import com.teamsun.mapper.DBCfgMapper;
import com.teamsun.mapper.ShowRptInfoMapper;
import com.teamsun.rptcfg.BaseRpt;
import com.teamsun.rptcfg.BaseRptColumn;
import com.teamsun.rptcfg.SelectDim;
import com.teamsun.rptcfg.TreeDim;
import com.teamsun.rptsql.DynamicSQL;
import com.teamsun.util.CommonUtils;
import com.teamsun.util.Constants;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;

import java.io.*;
import java.text.DecimalFormat;
import java.util.*;

/**
 * <p>查询报表服务实现类<p/>
 */
public class ShowRptServiceImpl implements IShowRptService {

    private static final Logger LOGGER = Logger.getLogger(ShowRptServiceImpl.class);

    @Autowired
    private DBCfgMapper dbCfgMapper;

    @Autowired
    private ShowRptInfoMapper showRptInfoMapper;

    /**
     * 查询报表数据
     * @param baseRpt  报表信息实体类
     * @param params 查询条件
     * @return
     */
    @Override
    public RptResult queryRptInfo(BaseRpt baseRpt, Map<String, String> params) throws ServiceException {
        // 查询数据库配置信息
        DBCfg dbCfg = null;
        try {
            dbCfg = queryDBCfg(baseRpt);
        } catch (Exception e) {
            LOGGER.error(e.getMessage(), e);
            throw new ServiceException("数据源信息查询失败,请联系管理员");
        }

        DynamicSQL dynamicSQL = new DynamicSQL(dbCfg.getDbType());

        String needSum = baseRpt.getNeedSum();  // 报表是否需要汇总
        String needAnalysis = baseRpt.getNeedAnalysis();  // 报表是否是分析报表

        // 动态拼接查询SQL
        String querySQL = dynamicSQL.getRptSql(baseRpt, params);
        LOGGER.info("查询报表[" + baseRpt.getRptName() + "][明细]的SQL为: " + querySQL);

        // 动态拼接查询数据条数SQL
        String countSQL = dynamicSQL.getRptCountSql(baseRpt, params);
        LOGGER.info("查询报表[" + baseRpt.getRptName() + "][数据量]的SQL为: " + countSQL);

        // 获前台页面取选取的维度信息
        // 如果不选择维度，则不需要查询汇总数据(因为如果不选择维度，则只有一条数据，查询汇总数据无意义)
        String selectDim = params.get(Constants.RPTDIM_KEY);
        if(Constants.YES.equals(needAnalysis) && StringUtils.isBlank(selectDim)) {
            needSum = Constants.NO;
        }

        String sumRptSQL = "";
        if(Constants.YES.equals(needSum)) {  // 汇总报表
            // 动态拼接汇总SQL
            sumRptSQL = dynamicSQL.getSumRptSql(baseRpt, params, querySQL);
            LOGGER.info("查询报表[" + baseRpt.getRptName() + "][汇总]的SQL为: " + sumRptSQL);
        }

        // 选择数据源
        DatasourceHolder.setDBName(dbCfg.getDbName());
        // 查询报表数据
        List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
        int totalCount = 0;
        try {
            resultList = showRptInfoMapper.queryRptData(querySQL);

            List<Map<String, Object>> sumList = new ArrayList<Map<String, Object>>();
            if (StringUtils.isNotBlank(sumRptSQL)) {
                // 选择数据源
                DatasourceHolder.setDBName(dbCfg.getDbName());
                // 查询汇总数据
                sumList = showRptInfoMapper.queryRptData(sumRptSQL);
            }

            // 选择数据源
            DatasourceHolder.setDBName(dbCfg.getDbName());
            // 查询数据数据量
            totalCount = showRptInfoMapper.queryTotalCount(countSQL);

            // 只有当有数据时才会显示汇总数据
            if (resultList != null && resultList.size() > 0) {
                if (sumList != null && sumList.size() > 0) {
                    resultList.addAll(sumList);
                    totalCount = totalCount + 1;
                }
            }
        } catch (Exception e) {
            LOGGER.error(e.getMessage(), e);
            throw new ServiceException("查询数据失败，请联系管理员");
        }

        RptResult rptResult = new RptResult();
        rptResult.setResultList(resultList);
        rptResult.setTotalCount(totalCount);
        return rptResult;
    }

    /**
     * 分页查询报表数据信息
     * @param baseRpt  报表信息实体类
     * @param params  查询条件
     * @param startIndex  分页起始行
     * @param endIndex  分页结束行
     * @param rows 每页显示条数
     * @return
     */
    @Override
    public RptResult queryRptInfoByPage(BaseRpt baseRpt, Map<String, String> params, int startIndex, int endIndex, int rows) throws ServiceException {
        // 查询数据库配置信息
        DBCfg dbCfg = null;
        try {
            dbCfg = queryDBCfg(baseRpt);
        } catch (Exception e) {
            LOGGER.error(e.getMessage(), e);
            throw new ServiceException("数据源信息查询失败，请联系管理员");
        }

        String needSum = baseRpt.getNeedSum();  // 报表是否需要汇总
        String needAnalysis = baseRpt.getNeedAnalysis();  // 报表是否是分析报表

        DynamicSQL dynamicSQL = new DynamicSQL(dbCfg.getDbType());
        // 动态拼接分页查询SQL
        String querySQL = dynamicSQL.getRptSqlByPage(baseRpt, params, startIndex, endIndex);
        LOGGER.info("报表[" + baseRpt.getRptName() + "][分页]查询SQL为: " + querySQL);

        // 动态拼接查询数据条数SQL
        String countSQL = dynamicSQL.getRptCountSql(baseRpt, params);
        LOGGER.info("查询报表[" + baseRpt.getRptName() + "][数据量]的SQL为: " + countSQL);

        // 获前台页面取选取的维度信息
        // 如果不选择维度，则不需要查询汇总数据(因为如果不选择维度，则只有一条数据，查询汇总数据无意义)
        String selectDim = params.get(Constants.RPTDIM_KEY);
        if(Constants.YES.equals(needAnalysis) && StringUtils.isBlank(selectDim)) {
            needSum = Constants.NO;
        }

        String sumRptSQLByPage = "";
        if(Constants.YES.equals(needSum)) {  // 汇总报表
            // 动态拼接汇总SQL
            // 汇总SQL查询所有数据的汇总，不是当页数据的汇总
            String sumQuerySQL = dynamicSQL.getRptSql(baseRpt, params);
            sumRptSQLByPage = dynamicSQL.getSumRptSqlByPage(baseRpt, params, sumQuerySQL);
            LOGGER.info("查询报表[" + baseRpt.getRptName() + "][汇总]的SQL为: " + sumRptSQLByPage);
        }

        // 选择数据源
        DatasourceHolder.setDBName(dbCfg.getDbName());
        // 查询报表数据
        List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
        int totalCount = 0;
        try {
            resultList = showRptInfoMapper.queryRptData(querySQL);

            List<Map<String, Object>> sumList = new ArrayList<Map<String, Object>>();
            if (StringUtils.isNotBlank(sumRptSQLByPage)) {
                // 选择数据源
                DatasourceHolder.setDBName(dbCfg.getDbName());
                // 查询汇总数据
                sumList = showRptInfoMapper.queryRptData(sumRptSQLByPage);
            }

            /**
             * 如果查询出的数据量等于页面每页显示的条数，则需要查询数据总量(此时数据总量可能会大于查询出的数据量)
             * 否则不需要查数据总量(数据总量即是查询出的数据量)
             */
            if (startIndex == 1 && resultList.size() < rows) {
                totalCount = resultList.size();
            } else {
                // 选择数据源
                DatasourceHolder.setDBName(dbCfg.getDbName());
                // 查询报表数据量
                totalCount = showRptInfoMapper.queryTotalCount(countSQL);
            }


            // 只有当有数据时才会显示汇总数据
            if (resultList != null && resultList.size() > 0) {
                if (sumList != null && sumList.size() > 0) {
                    resultList.addAll(sumList);
                    totalCount = totalCount + 1;
                }
            }
        } catch (Exception e) {
            LOGGER.error(e.getMessage(), e);
            throw new ServiceException("查询数据失败，请联系管理员");
        }

        RptResult rptResult = new RptResult();
        rptResult.setResultList(resultList);
        rptResult.setTotalCount(totalCount);
        return rptResult;
    }

    /**
     * 查询下拉框数据(下拉框数据来源于数据库码表)
     * @param selectDim 下拉框维表信息配置
     * @param rptInfo 下拉框所属的报表信息
     * @return
     */
    @Override
    public Map<String, String> querySelectValue(SelectDim selectDim, BaseRpt rptInfo) {
        Map<String, String> resultMap = new LinkedHashMap<String, String>();
        resultMap.put("", "请选择");

        // 动态拼接SQL
        DynamicSQL dynamicSQL = new DynamicSQL();
        String selectSQL = dynamicSQL.getSelectSql(selectDim);
        LOGGER.info("下拉框的查询SQL为: " + selectSQL);

        String dataSource = selectDim.getDataSource();
        // 如果配置数据源则先选择数据源，否则使用下拉框所属报表的数据源
        if(StringUtils.isNotBlank(dataSource)) {
            DBCfg dbCfg = queryDBCfgById(dataSource);
            DatasourceHolder.setDBName(dbCfg.getDbName());
        } else {
            // 查询数据库配置信息
            try {
                DBCfg dbCfg = queryDBCfg(rptInfo);
                DatasourceHolder.setDBName(dbCfg.getDbName());
            } catch (Exception e) {
                LOGGER.error(e.getMessage(), e);
            }
        }
        // 查询数据
        List<Map<String, Object>> selectData = showRptInfoMapper.queryDimData(selectSQL);
        if(selectData != null && selectData.size() > 0) {
            for(Map<String, Object> map : selectData) {
                String key = (String) map.get("key");
                String value = (String) map.get("value");
                resultMap.put(key, value);
            }
        }

        return resultMap;
    }

    /**
     * 查询树形数据
     * @param treeDim 树形数据维表信息配置
     * @param param 权限参数(code权限)
     * @param parentParam 权限参数(upcode权限)
     * @param parentId 父节点ID
     * @param rptInfo 树所属的下拉框信息
     * @return
     */
    @Override
    public List<Map<String, Object>> queryTreeData(TreeDim treeDim, String param, String parentParam, String parentId, BaseRpt rptInfo) {

        // 动态拼接SQL
        DynamicSQL dynamicSQL = new DynamicSQL();
        String treeSQL = dynamicSQL.getTreeSql(treeDim, param, parentParam, parentId);

        String dataSource = treeDim.getDataSource();
        // 如果配置数据源则先选择数据源，否则使用所属报表的数据源
        if(StringUtils.isNotBlank(dataSource)) {
            DBCfg dbCfg = queryDBCfgById(dataSource);
            DatasourceHolder.setDBName(dbCfg.getDbName());
        } else {
            try {
                // 查询数据库配置信息
                DBCfg dbCfg = queryDBCfg(rptInfo);
                DatasourceHolder.setDBName(dbCfg.getDbName());
            } catch (Exception e) {
                LOGGER.error(e.getMessage(), e);
            }
        }
        // 查询数据
        List<Map<String, Object>> treeData = showRptInfoMapper.queryDimData(treeSQL);

        return treeData;
    }

    /**
     * 查询树形控件信息(带搜索框的树形控件)
     * @param treeDim 维度表信息配置
     * @param param 权限条件(code权限)
     * @param parentParam 权限条件(upcode权限)
     * @param parentId 父节点ID
     * @param rptInfo 树所属的报表信息
     * @param searchName 搜索的名称
     * @return
     */
    @Override
    public List<Map<String, Object>> queryTreeData(TreeDim treeDim, String param, String parentParam, String parentId, BaseRpt rptInfo, String searchName) {
        // 动态拼接SQL
        DynamicSQL dynamicSQL = new DynamicSQL();
        String treeSQL = dynamicSQL.getTreeSqlBySearch(treeDim, param, parentParam, parentId, searchName);

        String dataSource = treeDim.getDataSource();
        // 如果配置数据源则先选择数据源，否则使用所属报表的数据源
        if(StringUtils.isNotBlank(dataSource)) {
            DBCfg dbCfg = queryDBCfgById(dataSource);
            DatasourceHolder.setDBName(dbCfg.getDbName());
        } else {
            try {
                // 查询数据库配置信息
                DBCfg dbCfg = queryDBCfg(rptInfo);
                DatasourceHolder.setDBName(dbCfg.getDbName());
            } catch (Exception e) {
                LOGGER.error(e.getMessage(), e);
            }
        }
        // 查询数据
        List<Map<String, Object>> treeData = showRptInfoMapper.queryDimData(treeSQL);

        return treeData;
    }

    /**
     * 查询树形控件信息(设置默认值)
     * @param treeDim 维度表信息配置
     * @param rptInfo 树所属的报表信息
     * @param defaultValue 默认已选的节点
     * @return
     */
    @Override
    public List<Map<String, Object>> queryTreeData(TreeDim treeDim, BaseRpt rptInfo, String defaultValue) {
        // 动态拼接SQL
        DynamicSQL dynamicSQL = new DynamicSQL();
        String treeSQL = dynamicSQL.getTreeSql(treeDim, defaultValue);

        String dataSource = treeDim.getDataSource();
        // 如果配置数据源则先选择数据源，否则使用所属报表的数据源
        if(StringUtils.isNotBlank(dataSource)) {
            DBCfg dbCfg = queryDBCfgById(dataSource);
            DatasourceHolder.setDBName(dbCfg.getDbName());
        } else {
            try {
                // 查询数据库配置信息
                DBCfg dbCfg = queryDBCfg(rptInfo);
                DatasourceHolder.setDBName(dbCfg.getDbName());
            } catch (Exception e) {
                LOGGER.error(e.getMessage(), e);
            }
        }
        // 查询数据
        List<Map<String, Object>> treeData = showRptInfoMapper.queryDimData(treeSQL);

        return treeData;
    }

    /**
     * 获取报表查询的SQL语句及报表对应的数据库信息
     * @param baseRpt  报表信息实体类
     * @param params  查询条件
     * @return
     */
    @Override
    public OffLineDownLoad getQuerySQL(BaseRpt baseRpt, Map<String, String> params) {
        // 查询数据库配置信息
        DBCfg dbCfg = null;
        try {
            dbCfg = queryDBCfg(baseRpt);
        } catch (Exception e) {
            LOGGER.error(e.getMessage(), e);
        }

        DynamicSQL dynamicSQL = new DynamicSQL(dbCfg.getDbType());

        // 动态拼接查询SQL
        String querySQL = dynamicSQL.getRptSql(baseRpt, params);
        LOGGER.info("查询报表[" + baseRpt.getRptName() + "][明细]的SQL为: " + querySQL);

        OffLineDownLoad offLineDownLoad = new OffLineDownLoad();
        offLineDownLoad.setDriver(dbCfg.getDriverClass());
        offLineDownLoad.setDbUrl(dbCfg.getJdbcURL());
        offLineDownLoad.setDbName(dbCfg.getUserName());
        offLineDownLoad.setDbPass(dbCfg.getPassword());
        offLineDownLoad.setSql(querySQL);

        return offLineDownLoad;
    }

    /**
     * 获取查询数据量的SQL
     * @param baseRpt 报表实体信息
     * @param params 查询条件
     * @return
     */
    @Override
    public String getCountSQL(BaseRpt baseRpt, Map<String, String> params) {
        // 查询数据库配置信息
        DBCfg dbCfg = null;
        try {
            dbCfg = queryDBCfg(baseRpt);
        } catch (Exception e) {
            LOGGER.error(e.getMessage(), e);
        }

        DynamicSQL dynamicSQL = new DynamicSQL(dbCfg.getDbType());

        // 获取查询数据量的SQL
        String countSQL = dynamicSQL.getRptCountSql(baseRpt, params);
        LOGGER.info("查询报表[" + baseRpt.getRptName() + "][数据量]的SQL为: " + countSQL);

        return countSQL;
    }

    /**
     * 获取查询SQL
     * @param baseRpt
     * @param params
     * @return
     */
    @Override
    public String getQuery(BaseRpt baseRpt, Map<String, String> params) {
        // 查询数据库配置信息
        DBCfg dbCfg = null;
        try {
            dbCfg = queryDBCfg(baseRpt);
        } catch (Exception e) {
            LOGGER.error(e.getMessage(), e);
        }

        DynamicSQL dynamicSQL = new DynamicSQL(dbCfg.getDbType());

        // 动态拼接查询SQL
        String querySQL = dynamicSQL.getRptSql(baseRpt, params);
        LOGGER.info("查询报表[" + baseRpt.getRptName() + "][明细]的SQL为: " + querySQL);
        querySQL = formatSQL(querySQL, dbCfg.getDbType());

        return querySQL;
    }

    /**
     * 获取分页查询SQL
     * @param baseRpt 报表实体信息
     * @param params 查询条件
     * @param startIndex 分页起始行
     * @param endIndex 分页结束行
     * @return
     */
    @Override
    public String getQuerySQLByPage(BaseRpt baseRpt, Map<String, String> params, int startIndex, int endIndex) {
        // 查询数据库配置信息
        DBCfg dbCfg = null;
        try {
            dbCfg = queryDBCfg(baseRpt);
        } catch (Exception e) {
            LOGGER.error(e.getMessage(), e);
        }

        DynamicSQL dynamicSQL = new DynamicSQL(dbCfg.getDbType());

        // 获取分页查询SQL
        String querySQL = dynamicSQL.getRptSqlByPage(baseRpt, params, startIndex, endIndex);
        LOGGER.info("报表[" + baseRpt.getRptName() + "][分页]查询SQL为: " + querySQL);
        // 格式化SQL
        querySQL = formatSQL(querySQL, dbCfg.getDbType());

        return querySQL;
    }

    /**
     * 获取报表汇总SQL
     * @param baseRpt 报表实体信息
     * @param params 查询条件
     * @return
     */
    @Override
    public String getSumSQL(BaseRpt baseRpt, Map<String, String> params) {
        // 查询数据库配置信息
        DBCfg dbCfg = null;
        try {
            dbCfg = queryDBCfg(baseRpt);
        } catch (Exception e) {
            LOGGER.error(e.getMessage(), e);
        }

        DynamicSQL dynamicSQL = new DynamicSQL(dbCfg.getDbType());

        // 动态拼接查询SQL
        String querySQL = dynamicSQL.getRptSql(baseRpt, params);

        // 动态拼接汇总SQL
        String sumRptSQL = dynamicSQL.getSumRptSql(baseRpt, params, querySQL);
        LOGGER.info("查询报表[" + baseRpt.getRptName() + "][汇总]的SQL为: " + sumRptSQL);

        return sumRptSQL;
    }

    /**
     * 查询报表数据量
     * @param baseRpt 报表实体信息
     * @param params 查询条件
     * @return
     */
    @Override
    public int getRptDataCount(BaseRpt baseRpt, Map<String, String> params) {
        // 查询数据库配置信息
        DBCfg dbCfg = null;
        try {
            dbCfg = queryDBCfg(baseRpt);
        } catch (Exception e) {
            LOGGER.error(e.getMessage(), e);
        }

        DynamicSQL dynamicSQL = new DynamicSQL(dbCfg.getDbType());

        // 获取查询数据量的SQL
        String countSQL = dynamicSQL.getRptCountSql(baseRpt, params);
        LOGGER.info("查询报表[" + baseRpt.getRptName() + "][数据量]的SQL为: " + countSQL);

        int totalCount = 0;
        // 选择数据源
        DatasourceHolder.setDBName(dbCfg.getDbName());
        // 查询报表数据量
        totalCount = showRptInfoMapper.queryTotalCount(countSQL);

        return totalCount;
    }

    /**
     * 分页获取报表信息
     * @param baseRpt 报表实体信息
     * @param params 查询条件
     * @param startIndex 分页起始行
     * @param endIndex 分页结束行
     * @return
     */
    @Override
    public List<Map<String, Object>> getRptDataByPage(BaseRpt baseRpt, Map<String, String> params, int startIndex, int endIndex) {
        // 查询数据库配置信息
        DBCfg dbCfg = null;
        try {
            dbCfg = queryDBCfg(baseRpt);
        } catch (Exception e) {
            LOGGER.error(e.getMessage(), e);
        }

        DynamicSQL dynamicSQL = new DynamicSQL(dbCfg.getDbType());

        // 获取分页查询SQL
        String querySQL = dynamicSQL.getRptSqlByPage(baseRpt, params, startIndex, endIndex);
        LOGGER.info("报表[" + baseRpt.getRptName() + "][分页]查询SQL为: " + querySQL);

        // 选择数据源
        DatasourceHolder.setDBName(dbCfg.getDbName());
        // 查询报表数据
        List<Map<String,Object>> resultList = showRptInfoMapper.queryRptData(querySQL);

        return resultList;
    }

    /**
     * 查询报表汇总数据
     * @param baseRpt 报表实体信息
     * @param params 查询条件
     * @return
     */
    @Override
    public List<Map<String, Object>> getRptDataSum(BaseRpt baseRpt, Map<String, String> params) {
        // 查询数据库配置信息
        DBCfg dbCfg = null;
        try {
            dbCfg = queryDBCfg(baseRpt);
        } catch (Exception e) {
            LOGGER.error(e.getMessage(), e);
        }

        DynamicSQL dynamicSQL = new DynamicSQL(dbCfg.getDbType());

        // 动态拼接查询SQL
        String querySQL = dynamicSQL.getRptSql(baseRpt, params);

        // 动态拼接汇总SQL
        String sumRptSQL = dynamicSQL.getSumRptSql(baseRpt, params, querySQL);
        LOGGER.info("查询报表[" + baseRpt.getRptName() + "][汇总]的SQL为: " + sumRptSQL);


        // 选择数据源
        DatasourceHolder.setDBName(dbCfg.getDbName());
        // 查询汇总数据
        List<Map<String,Object>> sumList  = showRptInfoMapper.queryRptData(sumRptSQL);

        return sumList;
    }

    /**
     * 报表导出(该方法目前废弃)
     * @param rptInfo  报表信息
     * @param rptId  报表ID
     * @param rptHeaderFilePath 报表表头Excel文件的路径
     * @param params 查询参数
     * @return
     */
    @Override
    @Deprecated
    public String downLoadExcel(BaseRpt rptInfo, String rptId, String rptHeaderFilePath, String selectRptIdx, String selectRptDim, Map<String, String> params) {
        String downLoadPath = "";
        try {
            List<BaseRptColumn> rptColumns = rptInfo.getRptColumns();

            // 将JSON字符串转换成TableHeader对象
            String rptHeaderJson = rptInfo.getRptHeader();
            List<List<TableHeader>> headers = JSON.parseObject(rptHeaderJson, new TypeReference<List<List<TableHeader>>>(){});
            int headerRows = headers.size();  // 表头行数

            String rptHeaderFileName = "RPT_" + rptId + ".xlsx";
            // 读取Excel模板并复制
            InputStream inputStream = new FileInputStream(rptHeaderFilePath + "/" + rptHeaderFileName);
            //InputStream inputStream = request.getSession().getServletContext().getResourceAsStream("/WEB-INF/rptHeader/" + rptHeaderFileName);

            String fileName = rptInfo.getRptName() + ".xlsx";
            String filePath = getRealPath();
            File newFile = new File(filePath, fileName);
            downLoadPath = newFile.getAbsolutePath();

            byte[] copyBytes = new byte[1024];
            int copyLen = 0;
            OutputStream outputStream = new FileOutputStream(newFile);
            while ((copyLen = inputStream.read(copyBytes)) != -1) {
                outputStream.write(copyBytes, 0, copyLen);
            }
            outputStream.flush();
            inputStream.close();
            outputStream.close();

            // 新文件写入数据
            FileInputStream fio = new FileInputStream(newFile);
            XSSFWorkbook workbook = new XSSFWorkbook(fio);
            XSSFSheet sheet = workbook.getSheetAt(0);

            FileOutputStream fos = new FileOutputStream(newFile);
            XSSFRow row = sheet.getRow(1);
            if(row == null) {
                row = sheet.createRow(1);
            }
            XSSFCell cell = row.getCell(0);
            if(cell == null) {
                cell = row.createCell(0);
            }

            // 查询报表数据
            RptResult rptResult = queryRptInfo(rptInfo, params);
            List<Map<String, Object>> rptDatas = rptResult.getResultList();
            List<String> idxList = new ArrayList<String>();
            List<String> dimList = new ArrayList<String>();
            if(StringUtils.isNotBlank(selectRptIdx)) {
                String[] idxs = selectRptIdx.split(",");
                idxList = Arrays.asList(idxs);
            }
            if(StringUtils.isNotBlank(selectRptDim)) {
                String[] dims = selectRptDim.split(",");
                for(String dim : dims) {
                    dimList.add(dim);
                }
                // 获取级联维度
                for(BaseRptColumn rptColumn : rptColumns) {
                    String aliasField = rptColumn.getAliasField();
                    String cascadeField = rptColumn.getCascade();
                    if(dimList.contains(aliasField) && StringUtils.isNotBlank(cascadeField)) {
                        dimList.add(cascadeField);
                    }
                }
            }
            if(rptDatas != null && rptDatas.size() > 0) {
                List<String> titleList = rptInfo.getExcelHeader();  // 按顺序获取Excel表头名称
                List<String> usedTitle = new ArrayList<String>();
                Set<String> percentTitle = new HashSet<String>();
                String needSum = rptInfo.getNeedSum();
                int dataSize = 0;
                if(Constants.YES.equals(needSum)) {
                    dataSize = rptDatas.size() - 1;
                } else {
                    dataSize = rptDatas.size();
                }
                XSSFCellStyle cellStyle = workbook.createCellStyle();
                XSSFCellStyle intStyle = workbook.createCellStyle();
                XSSFDataFormat dataFormat = workbook.createDataFormat();
                cellStyle.setDataFormat(dataFormat.getFormat("#,##0.00"));
                intStyle.setDataFormat(dataFormat.getFormat("#0"));
                DecimalFormat percentFormat = new DecimalFormat("#.##%");
                DecimalFormat decimalFormat = new DecimalFormat("0.00");

                for(int i=0; i<dataSize; i++) {
                    row = sheet.createRow(i + headerRows);
                    Map<String, Object> rptData = rptDatas.get(i);
                    usedTitle.clear();
                    for(int k=0; k<titleList.size(); k++) {
                        String excelTitle = titleList.get(k);
                        for(int j=0; j<rptColumns.size(); j++) {
                            BaseRptColumn rptColumn = rptColumns.get(j);
                            String title = rptColumn.getName();
                            String key = rptColumn.getAliasField();
                            if(title.equals(excelTitle) && !usedTitle.contains(key)) {
                                String colType= rptColumn.getColType();
                                String conditionType = rptColumn.getConditionType();
                                cell =row.createCell(k);
                                String strValue = "";
                                if(rptData.get(key) != null) {
                                    strValue = String.valueOf(rptData.get(key));

                                    // 如果列是查询列且类型为下拉框，则需要转义(将编码转换为名称)
                                    if(Constants.CONDITIONTYPE_SELECT.equals(conditionType) || Constants.CONDITIONTYPE_MULTI_SELECT.equals(conditionType)) {
                                        Map<String, String> optionValue = rptColumns.get(j).getOptionValue();
                                        strValue = optionValue.get(strValue);
                                    }
                                }

                                // 判断是否为数字
                                boolean isNumber = CommonUtils.isNumberic(strValue);
                                if(isNumber) {
                                    Double value = Double.parseDouble(strValue);
                                    if(Constants.NUMBERFORMATER_PERCENT_100.equals(rptColumn.getNumberFormat())) {
                                        String percent = percentFormat.format(value);
                                        cell.setCellValue(percent);
                                        percentTitle.add(key);
                                    } else if(Constants.NUMBERFORMATER_PERCENT.equals(rptColumn.getNumberFormat())) {
                                        String percent = decimalFormat.format(value);
                                        cell.setCellValue(percent + "%");
                                    } else {
                                        if(strValue.indexOf(".") > -1) {
                                            cell.setCellStyle(cellStyle);
                                            cell.setCellValue(value);
                                        } else {
                                            cell.setCellStyle(intStyle);
                                            cell.setCellValue(value);
                                        }
                                    }
                                } else {
                                    cell.setCellValue(strValue);
                                }

                                // 如果指标或维度未选中，在导出Excel时，将相关列设置为隐藏
                                if(StringUtils.isNotBlank(selectRptIdx)) {
                                    if(Constants.NO.equals(colType) && !idxList.contains(key)) {
                                        sheet.setColumnWidth(k, 0);
                                        //sheet.setColumnHidden(j, true);
                                    }
                                }
                                if(StringUtils.isNotBlank(selectRptDim)) {
                                    if(Constants.YES.equals(colType) && !dimList.contains(key)) {
                                        sheet.setColumnWidth(k, 0);
                                        //sheet.setColumnHidden(j, true);
                                    }
                                }
                                usedTitle.add(key);
                                break;
                            }
                        }
                    }
                }
                // 如果报表是汇总报表，则需要额外导出汇总行
                if(Constants.YES.equals(needSum)) {
                    Map<String, Object> rptData = rptDatas.get(dataSize);
                    row = sheet.createRow(dataSize + headerRows);
                    for(int m=0; m<usedTitle.size(); m++) {
                        String key = usedTitle.get(m);
                        cell =row.createCell(m);
                        String strValue = "";
                        if(rptData.get(key) != null) {
                            strValue = String.valueOf(rptData.get(key));
                        }

                        // 判断是否为数字
                        boolean isNumber = CommonUtils.isNumberic(strValue);
                        if(isNumber) {
                            Double value = Double.parseDouble(strValue);
                            if(percentTitle.contains(key)) {
                                String percent = percentFormat.format(value);
                                cell.setCellValue(percent);
                            } else {
                                if(strValue.indexOf(".") > -1) {
                                    cell.setCellStyle(cellStyle);
                                    cell.setCellValue(value);
                                } else {
                                    cell.setCellStyle(intStyle);
                                    cell.setCellValue(value);
                                }
                            }
                        } else {
                            cell.setCellValue(strValue);
                        }
                    }
                }
            }
            workbook.write(fos);
            fos.flush();
            fos.close();
            fio.close();
        } catch (Exception e) {
            LOGGER.error("报表导出失败", e);
        }

        return downLoadPath;
    }

    /**
     * 查询数据库配置信息
     * @param baseRpt
     * @return
     */
    private DBCfg queryDBCfg(BaseRpt baseRpt) throws Exception {
        DBCfg dbCfg = null;
        Map<String, Object> param = new HashMap<String, Object>();
        param.put("dbId", baseRpt.getDataSource());  // 数据源ID
        List<DBCfg> dbCfgs = dbCfgMapper.queryDBCfg(param);
        if (dbCfgs != null && dbCfgs.size() > 0) {
            dbCfg = dbCfgs.get(0);
        }
        return dbCfg;
    }

    /**
     * 根据数据源ID查询数据库配置
     * @param dbId
     * @return
     */
    private DBCfg queryDBCfgById(String dbId) {
        Map<String, Object> param = new HashMap<String, Object>();
        param.put("dbId", dbId);  // 数据源ID
        List<DBCfg> dbCfgs = dbCfgMapper.queryDBCfg(param);
        DBCfg dbCfg = null;
        if(dbCfgs != null && dbCfgs.size() > 0) {
            dbCfg = dbCfgs.get(0);
        }
        return dbCfg;
    }

    private String getRealPath() {
        String path = this.getClass().getClassLoader().getResource("/").getPath();
        String[] paths = path.split("/");
        String realPath = "";
        for (int i = 0; i < paths.length - 2; i++) {
            realPath += paths[i] + "/";
        }
        System.out.println("======" + realPath);
        return realPath;
    }

    /**
     * 格式化SQL
     * @param oriSQL
     * @param dbType
     * @return
     */
    private String formatSQL(String oriSQL, String dbType) {
        String formatSQL = "";
        if(Constants.DBTYPE_MYSQL.equals(dbType)) {
            formatSQL = SQLUtils.formatMySql(oriSQL);
        } else if(Constants.DBTYPE_ORACLE.equals(dbType)) {
            formatSQL = SQLUtils.formatOracle(oriSQL);
        }

        return formatSQL;
    }
}
